import time
import hmac
import hashlib
import base64
import urllib.parse
import requests
import json
import pandas as pd
from sqlalchemy import create_engine, text

# ================= MySQL 连接配置 =================
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/cn_stock_data")

# ================= 钉钉机器人配置 =================
access_token = "16bb331727340eaea9ab212c0d0ddb5148f60456b4cc9e6a02c35578796c20b3"
secret = "SEC3758a8faa7e911b364aa8217ea59e350022a007f11c6a217d2bd118a4f9dd52a"

def send_dingtalk_message(content: str, chunk_size: int = 4000):
    """分批发送钉钉消息"""
    chunks = [content[i:i+chunk_size] for i in range(0, len(content), chunk_size)]
    for idx, chunk in enumerate(chunks, start=1):
        timestamp = str(round(time.time() * 1000))
        string_to_sign = f"{timestamp}\n{secret}"
        hmac_code = hmac.new(secret.encode("utf-8"),
                             string_to_sign.encode("utf-8"),
                             hashlib.sha256).digest()
        sign = urllib.parse.quote_plus(base64.b64encode(hmac_code))
        webhook_url = f"https://oapi.dingtalk.com/robot/send?access_token={access_token}&timestamp={timestamp}&sign={sign}"
        headers = {"Content-Type": "application/json"}
        data = {"msgtype": "text", "text": {"content": chunk}}
        response = requests.post(webhook_url, headers=headers, data=json.dumps(data))
        print(f"📤 发送第 {idx}/{len(chunks)} 条消息, 状态码: {response.status_code}")
        time.sleep(0.5)

def main():
    days = 30
    ma_window = 10
    need_rows = days + ma_window - 1  # 39

    # Step1: 每只股票取最近 need_rows 条记录
    sql = text("""
        WITH ranked AS (
            SELECT
                stock_code,
                trade_date,
                close_price,
                ROW_NUMBER() OVER (PARTITION BY stock_code ORDER BY trade_date DESC) AS rn
            FROM his_daily_market_data
        )
        SELECT stock_code, trade_date, close_price
        FROM ranked
        WHERE rn <= :need_rows
    """)
    with engine.begin() as conn:
        df = pd.read_sql(sql, conn, params={"need_rows": need_rows})
    if df.empty:
        print("⚠️ 没有查询到数据")
        return

    df["trade_date"] = pd.to_datetime(df["trade_date"])
    df["close_price"] = pd.to_numeric(df["close_price"], errors="coerce")
    df = df.dropna(subset=["close_price"])

    # Step2: 计算30天收盘价在10日均线上方比例
    result_list = []
    for stock_code, g in df.groupby("stock_code", sort=False):
        g = g.sort_values("trade_date")
        g["ma10"] = g["close_price"].rolling(window=ma_window, min_periods=ma_window).mean()
        recent = g.tail(days)
        if len(recent) < days:
            continue
        above = (recent["close_price"] > recent["ma10"]).sum()
        pct = above / days * 100
        result_list.append((stock_code, pct))

    if not result_list:
        print("⚠️ 没有股票满足条件")
        return

    # Step3: 取前200
    top200 = pd.DataFrame(result_list, columns=["stock_code", "pct_above"])\
                .sort_values("pct_above", ascending=False)\
                .head(200)

    # Step4: 补充公司信息
    with engine.begin() as conn:
        info_df = pd.read_sql(text("""
            SELECT stock_code, stock_name, industry, total_market_value
            FROM company_info
        """), conn)
    merged = pd.merge(top200, info_df, on="stock_code", how="left")

    # Step5: 获取去年年报净利润用于筛选，及最新财报
    with engine.begin() as conn:
        # 去年年报
        last_year = pd.read_sql(text("""
            SELECT ps.stock_code, ps.net_profit AS net_profit_last_year
            FROM profit_statement ps
            WHERE ps.report_date = (
                SELECT MAX(report_date) 
                FROM profit_statement 
                WHERE YEAR(report_date) = YEAR(CURDATE()) - 1 AND stock_code = ps.stock_code
            )
        """), conn)
        # 最新财报
        latest_profit_df = pd.read_sql(text("""
            SELECT ps.stock_code, ps.net_profit AS net_profit_latest, ps.net_profit_yoy, ps.report_date
            FROM profit_statement ps
            INNER JOIN (
                SELECT stock_code, MAX(report_date) AS max_report_date
                FROM profit_statement
                GROUP BY stock_code
            ) t ON ps.stock_code = t.stock_code AND ps.report_date = t.max_report_date
        """), conn)

    merged = pd.merge(merged, last_year, on="stock_code", how="left")
    merged = pd.merge(merged, latest_profit_df, on="stock_code", how="left")

    # Step6: 筛选去年年报净利润 > 0
    top_filtered = merged[merged["net_profit_last_year"] > 0].copy()

    # Step7: 计算 PE（用去年年报净利润保证正值）
    top_filtered["PE"] = top_filtered.apply(
        lambda r: (r["total_market_value"] / r["net_profit_last_year"]) if r["net_profit_last_year"] > 0 else None,
        axis=1
    )

    # Step8: 取最终 Top50
    final_top50 = top_filtered.sort_values("pct_above", ascending=False).head(50)

    # Step9: 拼接消息，显示最新报告期净利润
    lines = ["📊 最近30个交易日收盘价在10日均线上方占比 Top 50（含行业 & 最新财报净利润）："]
    for _, row in final_top50.iterrows():
        net_profit_w = row["net_profit_latest"] / 1e4 if pd.notna(row["net_profit_latest"]) else float('nan')
        pe = row["PE"] if pd.notna(row["PE"]) else float('nan')
        net_profit_yoy = row.get("net_profit_yoy", float('nan'))
        pct_above = row["pct_above"]

        lines.append(
            f"{row['stock_code']} {row.get('stock_name','')} | "
            f"{row.get('industry','')} | "
            f"比例: {pct_above:.2f}% | "
            f"净利润(万): {net_profit_w:.2f} | "
            f"PE: {pe:.2f} | "
            f"净利润增速: {net_profit_yoy:.2f}% | "
            f"报告期: {row.get('report_date','')}"
        )

    send_dingtalk_message("\n".join(lines))

if __name__ == "__main__":
    main()
